﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.Sql;
using System.Data.SqlClient;



using SString = System.String;

namespace SharepointConnectivity
{
    public partial class Form1 : Form
    {
        #region GlobalVariables
        string logmsg;
        string tempstr;
        string[] scripts = new string[100];
        string[] fields = new string[100];     
        string[] tables = new string[100];
        bool initialized = false;

        string datapath = "C:\\special-projects\\SharepointConnectivity\\SharepointConnectivity\\obj\\Data.txt";
        
        #endregion
        
        #region SQLConnection

        SqlConnection mySqlConnection;
        SqlCommand mySqlCommand;
        SqlDataReader myDataReader;
        public Form1()
        {
            InitializeComponent();
            string[] stringarray = ReadData(datapath);
            ParseData(stringarray);
            char[] delimiterChars = { '|' };
            foreach (string s in scripts)
            {
                if (string.IsNullOrEmpty(s)) { break; }
                string[] scriptrow = s.Split(delimiterChars);
                string scriptname = scriptrow[0];
                cScriptChooser.Items.Add(scriptname);

            }
            foreach (string s in fields)
            {
                
                if (string.IsNullOrEmpty(s)) { break; }
                string[] fieldrow = s.Split(delimiterChars);
                if (fieldrow.Count() < 2) { break; }
                string field = fieldrow[1];
                cScriptFieldChooser.Items.Add(s);
            }
            foreach (string s in tables)
            {

                if (string.IsNullOrEmpty(s)) { break; }
                cScriptTableName.Items.Add(s);
            }
            initialized = true;
        }
        
        public void closeConn()//Close SQL Connection
        {
            if (mySqlConnection != null)
            {
                if (mySqlConnection.State == ConnectionState.Open)
                {
                    mySqlConnection.Close();
                }
                mySqlConnection.Dispose();
            }
        }
        public SqlConnection createConn()//Create SQL Connection
        {

            string server = "TIF03ITS3SP302";
            //string list = tDataBase.Text;
            string database = "WSS_Content_8a19a6f9c60b462790d4bebf0b98d6f2";
            string myConnString = string.Format("Server={0};Database={1};Integrated Security=SSPI;",server,database);
            //LIST={238d329e-d87f-42a7-9b4b-738c597755be}

            //string myConnString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=2;RetrieveIds=Yes;DATABASE='{0}';LIST={'{1}'}",database,list);
            if (mySqlConnection == null) { mySqlConnection = new SqlConnection(); };

            if (mySqlConnection.ConnectionString == string.Empty || mySqlConnection.ConnectionString == null)
            {
                try
                {
                    mySqlConnection.ConnectionString = myConnString;
                    mySqlConnection.Open();
                    logmsg = string.Format("Connected To: {0}", database) + "\r\n";
                    ReadDisplay.AppendText(logmsg);
                }
                catch (Exception)
                {
                    if (mySqlConnection.State != ConnectionState.Closed)
                    {
                        mySqlConnection.Close();
                    }
                    try
                    {
                        mySqlConnection.Open();
                        logmsg = string.Format("Connected To: {0}", database) + "\r\n";
                        ReadDisplay.AppendText(logmsg);
                    }
                    catch (Exception ex)
                    {
                        logmsg = string.Format("Error Connecting To: {0}", database) + "\r\n";
                        ReadDisplay.AppendText(logmsg);
                    }

                }
                return mySqlConnection;
            }
            if (mySqlConnection.State != ConnectionState.Open)
            {
                try
                {
                    mySqlConnection.ConnectionString = myConnString;
                    mySqlConnection.Open();
                    logmsg = string.Format("Connected To: {0}", database) + "\r\n";
                    ReadDisplay.AppendText(logmsg);
                }
                catch (Exception)
                {
                
                    if (mySqlConnection.State != ConnectionState.Closed)
                    {
                        mySqlConnection.Close();
                    }
                    try
                    {
                        mySqlConnection.ConnectionString = myConnString;
                        mySqlConnection.Open();
                        logmsg = string.Format("Connected To: {0}", database) + "\r\n";
                        ReadDisplay.AppendText(logmsg);
                    }
                    catch (Exception ex)
                    {
                        logmsg = string.Format("Error Connecting To: {0}", database) + "\r\n";
                        ReadDisplay.AppendText(logmsg);
                    }
                }
            }
            return mySqlConnection;
        }
        public void closeread()//Close Read Session
        {
            try
            {

                myDataReader.Close();
                mySqlCommand.Dispose();
            }
            catch (Exception ex)
            {
            }
        }
        public void execwrite(string sqlcmd)//Execute Write Command
        {
            try
            {
                mySqlCommand = mySqlConnection.CreateCommand();
                mySqlCommand.CommandText = sqlcmd;
                mySqlCommand.ExecuteNonQuery();
                mySqlCommand.Dispose();
            }
            catch (Exception ex)
            {
            }
        }
        public void execread(string sqlcmd)//Open Read Session, Execute Read Command
        {
            try
            {
                mySqlCommand = mySqlConnection.CreateCommand();
                mySqlCommand.CommandText = sqlcmd;
                myDataReader = mySqlCommand.ExecuteReader();
            }
            catch (Exception ex)
            {
            }
        }
        private void bConnect_Click(object sender, EventArgs e)
        {
            
            createConn();
            string strpass = "";

            
            int i = 0;
            //while (myDataReader.Read())
            //{

                
                //ReadDisplay.AppendText(logmsg);
                //ReadDisplay.AppendText("\r\n");

              //  i++;


            //}
            //ReadDisplay.AppendText(i.ToString());
            //closeread();
        }
        private void bClose_Click(object sender, EventArgs e)
        {
            closeConn();
        }
        private int countparam(string text)
        {
            char[] delimiterChars = { ',' };
            string[] words = text.Split(delimiterChars);
            return words.Count();
        }
        public string GetTempPath()
        {
            string path = System.Environment.GetEnvironmentVariable("TEMP");
            if (!path.EndsWith("\\")) path += "\\";
            return path;
        }
        public void CreateData(string msg)//Write string to text file
        {
            System.IO.StreamWriter sw = System.IO.File.CreateText(datapath);

            try
            {
                string logLine = System.String.Format(msg);
                sw.WriteLine(logLine);
            }
            finally
            {
                sw.Close();
            }
        }
        public void WriteData(string msg)//Write string to text file
        {
            System.IO.StreamWriter sw = System.IO.File.AppendText(datapath);

            try
            {
                string logLine = System.String.Format(msg);
                sw.WriteLine(logLine);
            }
            finally
            {
                sw.Close();
            }
        }
        public string[] ReadData(string filename)
        {
            System.IO.StreamReader sr;
            string[] stringarray = new string[100];
            string s;
            sr = System.IO.File.OpenText(filename);
            s = sr.ReadLine();
            int i = 0;
            stringarray[i] = s;
            i++;
            while (s != null)
            {
                s = sr.ReadLine();
                stringarray[i] = s;
                i++;

            }
            int count = i;
            sr.Close();
            for (i = 0; i < (count-1);i++)
            {
                ReadDisplay.AppendText(stringarray[i]);
                ReadDisplay.AppendText("\r\n");

            }
            return stringarray;
        }
        public void ParseData(string[] stringarray)
        {
            int fieldcount = 0;
            int scriptcount = 0;
            int tablecount = 0;
            string type = "field";
            foreach (string s in stringarray)
            {
                
                switch (s)
                {
                    case "#region FIELD":
                        type = "field";
                        break;
                    case "#region SCRIPT":
                        type = "script";
                        break;
                    case "#region TABLE":
                        type = "table";
                        break;
                    case "#endregion":
                        break;                
                        

                    default:
                        if (string.IsNullOrEmpty(s)) { break; }
                        switch (type)
                        {
                            case "field":
                                fields[fieldcount] = s;
                                fieldcount++;
                                break;
                            case "script":
                                scripts[scriptcount] = s;
                                scriptcount++;
                                break;
                            case "table":
                                tables[tablecount] = s;
                                tablecount++;
                                break;
                            default:
                                break;
                        }
                        break;
                }
            }
        }
        
        #endregion
        #region Debugging
        private void bRunQuery_Click(object sender, EventArgs e)
        {
            try
            {
                
                string sqlquery = tQuery.Text;
                
                
                int count = countparam(sqlquery);
                ReadDisplay.AppendText(logmsg);
                execread(sqlquery);
                while (myDataReader.Read())
                {
                    for (int i = 0; i < count; i++)
                    {

                        logmsg = string.Format("{0}", myDataReader[i]);
                        ReadDisplay.AppendText(logmsg);
                        ReadDisplay.AppendText("!");
                        
                    }
                    ReadDisplay.AppendText("\r\n");
                }
                closeread();
            }
            catch (Exception Ex)
            {
                ReadDisplay.Clear();
                ReadDisplay.AppendText("Error with Query String");
                ReadDisplay.AppendText("\r\n");
                logmsg = Ex.Message;
                ReadDisplay.AppendText(logmsg);
                ReadDisplay.AppendText("\r\n");

            }


        }

        #endregion
        #region Script       


        private void tabScript_Enter(object sender, EventArgs e)
        {
            
            //foreach (string field in fieldname)
            //{
            //    cScriptFieldName.Items.Add(field);
            //}
            
        }

        private void cScriptChooser_SelectedIndexChanged(object sender, EventArgs e)
        {

            int i = cScriptChooser.SelectedIndex;
            char[] delimiterChars = { '|' };

            string[] scriptrow = scripts[i].Split(delimiterChars);
            string scriptquery = scriptrow[1];;
            tScriptQuery.Text = scriptquery;

        }
        private void bScriptNew_Click(object sender, EventArgs e)
        {
            tScriptQuery.Clear();
            tScriptQuery.Text = "SELECT FROM";
        }

        private void bScriptAddTable_Click(object sender, EventArgs e)
        {
            string from = "FROM";
            int fromindex = 0;
            string tablename = cScriptTableName.Text;
            string s = tScriptQuery.Text;
            fromindex = s.IndexOf(from);
            string snew = string.Concat(s.Substring(0, fromindex), from);
            snew = string.Concat(snew, " ",tablename);
            
            tScriptQuery.Text = snew;


        }
        public void bScriptAddField_Click(object sender, EventArgs e)
        {
            string group = "select";
            string select = "SELECT ";
            int selectindex = 0;
            string from = "FROM";
            int fromindex = 0;
            char[] delimiterChars = { '|' };
            string[] fields = cScriptFieldChooser.Text.Split(delimiterChars);
            string fieldname;
            try
            {
                 fieldname = fields[1];
            }
            catch (Exception ex)
            {
                fieldname = "";
            }
            
            
            
            string s = tScriptQuery.Text;
            selectindex = s.IndexOf(select) + select.Length;
            fromindex = s.IndexOf(from);
            string snew = string.Concat(s.Substring(0, selectindex),s.Substring(selectindex,(fromindex - 7)), " ", fieldname, " ");
            snew = string.Concat(snew, s.Substring(fromindex, (s.Length - fromindex)));
            char[] delimiterChars2 = { ' ',',' };
            string[] words = snew.Split(delimiterChars2);
            string word;
            for(int i = 0; i < words.Count(); i ++)
            {
                word = words[i];
                switch (word)
                {
                    case "SELECT":
                        group = "select";
                        break;
                    case "FROM":
                        group = "from";
                        break;
                    case "WHERE":
                        break;
                    default:
                        switch (group)
                        {
                            case "select":
                                if (!string.IsNullOrEmpty(words[i]) & (!string.Equals(words[i+1],"FROM")))
                                {
                                    words[i] = words[i] + ',';
                                }
                                break;
                            default:
                                break;
                        }
                        break;
                }
            }
            string snew2 = "";
            foreach(string newword in words)
            {
                snew2 = snew2 + newword + " ";
            }
            tScriptQuery.Text = snew2;


        }
        private void bScriptSave_Click(object sender, EventArgs e)
        {
            CreateData("#region FIELD");
            foreach (string field in fields)
            {
                if (!string.IsNullOrEmpty(field))
                {
                    WriteData(field);
                }
            }
            WriteData("#endregion");
            WriteData("#region TABLE");
            foreach (string table in tables)
            {
                if (!string.IsNullOrEmpty(table))
                {
                    WriteData(table);
                }
            }
            WriteData("#endregion");
            WriteData("#region SCRIPT");
            foreach (string script in scripts)
            {
                if (!string.IsNullOrEmpty(script))
                {
                    WriteData(script);
                }
            }
            string s = tScriptReportName.Text + "|" + tScriptQuery.Text;
            WriteData(s);
            WriteData("#endregion");
        }





        #endregion















    }
}

